๐Ÿ›ข Normalization

@Seongukbaek Posted August 21, 2022 ยท Updated August 21, 2022 ยท 9 min read

๐Ÿงท ์ด์ƒ(Anomaly)

์ด์ƒ์€ ํ…Œ์ด๋ธ” ์„ค๊ณ„ ์‹œ ์ž˜๋ชป๋œ ์„ค๊ณ„๋กœ ์ธํ•ด ๋ฐ์ดํ„ฐ๋ฅผ ์‚ฝ์ž…, ์‚ญ์ œ, ์ˆ˜์ •ํ•  ๋•Œ ๋…ผ๋ฆฌ์ ์œผ๋กœ ์ƒ๊ธฐ๋Š” ์˜ค๋ฅ˜๋ฅผ ๋งํ•œ๋‹ค.

๐Ÿชš ์‚ฝ์ž… ์ด์ƒ

ํŠœํ”Œ ์‚ฝ์ž… ์‹œ, ํŠน์ • ์†์„ฑ์— ํ•ด๋‹นํ•˜๋Š” ๊ฐ’์ด ์—†์–ด NULL์„ ์ž…๋ ฅํ•ด์•ผ ํ•˜๋Š” ์ด์ƒ์ด๋‹ค.

์ด๋Š” ์—”ํ‹ฐํ‹ฐ ๋ฌด๊ฒฐ์„ฑ ์ œ์•ฝ์กฐ๊ฑด์„ ์œ„๋ฐ˜ํ•  ์ˆ˜ ์žˆ๋‹ค.

๐Ÿชš ์‚ญ์ œ ์ด์ƒ

ํŠœํ”Œ ์‚ญ์ œ ์‹œ, ๊ฐ™์ด ์ €์žฅ๋œ ๋‹ค๋ฅธ ์ •๋ณด๊นŒ์ง€ ์—ฐ์‡„์ ์œผ๋กœ ์‚ญ์ œ๋˜๋Š” ์ด์ƒ์ด๋‹ค.

์ด๋Š” 2๊ฐœ์˜ ๋‹ค๋ฅธ ์ •๋ณด๋“ค์ด ํ•จ๊ป˜ ์ €์žฅ๋˜์–ด ๋ฐœ์ƒํ•˜๋Š” ๋ฌธ์ œ์ด๋‹ค. ์ด๋ฅผ ๋ถ„๋ฆฌํ•˜์—ฌ ๊ด€๋ฆฌํ•œ๋‹ค๋ฉด, ํ•ด๊ฒฐํ•  ์ˆ˜ ์žˆ์„ ๊ฒƒ์ด๋‹ค.

๐Ÿชš ์ˆ˜์ • ์ด์ƒ

์ค‘๋ณต๋œ ๋ฐ์ดํ„ฐ ์ค‘ ์ผ๋ถ€๋งŒ ์ˆ˜์ •๋˜์–ด ๋ฐ์ดํ„ฐ ๋ชจ์ˆœ์ด ๋ฐœ์ƒํ•˜๋Š” ์ด์ƒ์œผ๋กœ, ๊ฐฑ์‹  ์ด์ƒ์ด๋ผ๊ณ ๋„ ๋ถˆ๋ฆฐ๋‹ค.

๋ฐ์ดํ„ฐ์˜ ์ผ๊ด€์„ฑ ์œ ์ง€๋ฅผ ์œ„ํ•ด ๋ชจ๋“  ์ค‘๋ณต๋œ ๊ฐ’์„ ๊ฐฑ์‹ ํ•˜์ง€ ์•Š๋Š”๋‹ค๋ฉด ๋ฐœ์ƒํ•  ๊ฒƒ์ด๋‹ค.


๐Ÿงท ํ•จ์ˆ˜ ์ข…์†์„ฑ(Functional Dependency)

DB์˜ attributes sets๊ฐ„์˜ ์ œ์•ฝ์กฐ๊ฑด๊ณผ ํŠน์„ฑ์„ ๋‚˜ํƒ€๋‚ด๋Š” ๊ฒƒ์œผ๋กœ, ์–ด๋–ค ์†์„ฑ A์˜ ๊ฐ’์„ ์•Œ๋ฉด, ๋‹ค๋ฅธ ์†์„ฑ B์˜ ๊ฐ’์ด ์œ ์ผํ•˜๊ฒŒ ์ •ํ•ด์ง€๋Š” ๊ด€๊ณ„๋ฅผ ์ข…์†์„ฑ์ด ์žˆ๋‹ค๊ณ  ํ•œ๋‹ค.

  • ์œ„ ํ‘œ์—์„œ, ํ•™์ƒ๋ฒˆํ˜ธ์™€ ํ•™์ƒ์ด๋ฆ„์€ ์˜์กด์„ฑ์ด ์กด์žฌํ•œ๋‹ค. ํ•™์ƒ ๋ฒˆํ˜ธ๋ฅผ ์•Œ๋ฉด, ํ•™์ƒ ์ด๋ฆ„์„ ์œ ์ผํ•˜๊ฒŒ ์•Œ ์ˆ˜ ์žˆ๊ธฐ ๋•Œ๋ฌธ์ด๋‹ค. ({ํ•™์ƒ ๋ฒˆํ˜ธ} -> {ํ•™์ƒ ์ด๋ฆ„})
  • ๋˜ํ•œ, ํŠน์ • ์†์„ฑ์˜ ์ง‘ํ•ฉ์ด ํŠน์ • ์†์„ฑ์„ ์œ ์ผํ•˜๊ฒŒ ์‹๋ณ„ํ•  ์ˆ˜๋„ ์žˆ๋‹ค. (ex. {ํ•™์ƒ ๋ฒˆํ˜ธ, ํ•™์ƒ ์ด๋ฆ„} -> {ํ•™๊ณผ})

๊ทธ๋ฆฌ๊ณ  ์ด๋Ÿฌํ•œ FD ์ œ์•ฝ์กฐ๊ฑด์€ ๋ชจ๋“  relation instance์—์„œ ์ง€์ผœ์ ธ์•ผ ํ•œ๋‹ค.

  • K๋ผ๋Š” ์†์„ฑ์ด Relation R์˜ Key๋ผ๋ฉด, K๋Š” R์˜ ๋ชจ๋“  ์†์„ฑ์„ ์œ ์ผํ•˜๊ฒŒ ์‹๋ณ„ํ•ด์•ผ ํ•œ๋‹ค.
  • X -> Y ๋ผ๊ณ  ํ•ด๋„, ๋ฐ˜๋“œ์‹œ Y -> X์ธ ๊ฒƒ์€ ์•„๋‹ˆ๋‹ค.

๐Ÿงท ์ •๊ทœํ™”(Normalization)

์ด์ƒํ˜„์ƒ์ด ๋ฐœ์ƒํ•˜๋Š” relation์„ ๋ถ„ํ•ดํ•˜์—ฌ ๋” ์ž‘์€ relation์œผ๋กœ ๋งŒ๋“ค์–ด ์ด์ƒํ˜„์ƒ์„ ์—†์• ๊ณ , ์ค‘๋ณต์„ ์ตœ์†Œํ™”ํ•˜๊ธฐ ์œ„ํ•œ ๊ณผ์ •์„ ์˜๋ฏธํ•œ๋‹ค.

์ด์— ๋ฐ˜๋Œ€๋˜๋Š” ๊ฐœ๋…์œผ๋กœ ๋น„์ •๊ทœํ™”(Denormalization)๊ฐ€ ์žˆ๋‹ค.

  • ๋•Œ๋กœ๋Š” ์„ฑ๋Šฅ์ƒ์˜ ์ด์œ ๋กœ, ์ž‘์€ ์ •๊ทœํ˜•์— ์žˆ๋Š” relation์„ base relation์œผ๋กœ ์‚ฌ์šฉํ•˜๊ธฐ๋„ ํ•œ๋‹ค.
  • ์ •๊ทœํ™”๊ฐ€ ๋ฌด์กฐ๊ฑด์œผ๋กœ ์ข‹์€ ๊ฒƒ์€ ์•„๋‹ˆ๋ผ๋Š” ๋ณด์—ฌ์ค€๋‹ค.

๐Ÿชš Normal Form

degree๋ฅผ ์‚ฌ์šฉํ•ด ์ •๊ทœํ™”์˜ ์ •๋„๋ฅผ ๋‚˜ํƒ€๋‚ผ ์ˆ˜ ์žˆ๋Š”๋ฐ, ์ด๋ฅผ ์ •๊ทœํ˜•์ด๋ผ ํ•œ๋‹ค.

์ •๊ทœํ˜•์˜ ๋ ˆ๋ฒจ์€ ๋‹ค์Œ๊ณผ ๊ฐ™๋‹ค.

  • 1NF
  • 2NF
  • 3NF
  • Boyce-Codd NF(BCNF)

์ •๊ทœํ˜•์˜ ๋ ˆ๋ฒจ์€ ์ฆ๊ฐ€ํ• ์ˆ˜๋ก, ์ •๊ทœํ™”์˜ ์ •๋„๊ฐ€ ์—„๊ฒฉํ•ด์ง„๋‹ค.

  • ๊ทธ๋ฆฌ๊ณ  ์ด๋Š” ์„ฑ๋Šฅ๊ณผ ํƒ€ํ˜‘ํ•˜์—ฌ ์กฐ์ ˆํ•œ๋‹ค.

๐Ÿชš First Normal Form(1NF)

๊ฐ€์žฅ flatํ•œ relational model์ด๋‹ค. ๋ชจ๋“  ์†์„ฑ ๊ฐ’์ด ์›์ž๊ฐ’(๋”์ด์ƒ ๋‚˜๋ˆ ์ง€์ง€ ์•Š๋Š” ๊ฐ’)์„ ๊ฐ–๋Š” ๊ฒฝ์šฐ์ด๋‹ค.

ํ•ด๋‹น ์ •๊ทœํ˜•์—์„œ ํ—ˆ์šฉํ•˜์ง€ ์•Š๋Š” ๊ฒƒ์€ ๋‹ค์Œ๊ณผ ๊ฐ™๋‹ค.

  • Composite Attributes
  • Multi-valued Attributes
  • Nested relations

    • ๊ฐ ํŠœํ”Œ์˜ ์†์„ฑ๊ฐ’์ด non-atomic(๋ถ„ํ•ด ๊ฐ€๋Šฅ)ํ•œ ๊ฒฝ์šฐ
    • Relations ์•ˆ์˜ relations

๐Ÿชš Second Normal Form(2NF)

2NF์— ๋Œ€ํ•ด ๋ฐฐ์šฐ๊ธฐ ์ „์— ์•„๋ž˜ ๊ฐœ๋…์„ ๋จผ์ € ๋‹ค๋ฃจ๊ณ  ๋„˜์–ด๊ฐ€๋„๋ก ํ•œ๋‹ค.

(Non) Prime Attributes

  • Prime Attribute๋ž€, Key Attribute์— ํฌํ•จ๋˜๋Š” ์†์„ฑ์„ ์˜๋ฏธํ•˜๊ณ , Non Prime Attribute๋ž€, ๊ทธ๋ ‡์ง€ ์•Š์€ ์†์„ฑ์„ ์˜๋ฏธํ•œ๋‹ค.
  • ์—ฌ๊ธฐ์„œ Key Attribute๋Š” ๋ชจ๋“  candidate key๋ฅผ ์˜๋ฏธํ•œ๋‹ค.

2NF๋Š” relation์ด 1NF์„ ๋งŒ์กฑํ•˜๊ณ , ์™„์ „ํ•œ PK ์ง‘ํ•ฉ๋งŒ์œผ๋กœ relation์˜ non-prime attribute๊ฐ€ ๊ฒฐ์ •๋˜๋Š” ํ˜•ํƒœ์ด๋‹ค.

  • ์ด๋ฅผ Full Functional Dependency๋ฅผ ๋งŒ์กฑํ•œ๋‹ค๊ณ  ํ•œ๋‹ค.
  • ์œ„์—์„œ, {ํ•™์ƒ ๋ฒˆํ˜ธ, ๊ฐ•์ขŒ ์ด๋ฆ„}์˜ ๋ณตํ•ฉ ํ‚ค๋ฅผ ๊ฐ€์ง€๊ณ  ์žˆ๋‹ค๊ณ  ๊ฐ€์ •ํ•œ๋‹ค.
  • ํ•™์ƒ ๋ฒˆํ˜ธ์™€ ๊ฐ•์ขŒ ์ด๋ฆ„์ด ์žˆ์–ด์•ผ ์„ฑ์ ์ด๋ผ๋Š” non-prime attribute๋ฅผ ๊ฒฐ์ •ํ•œ๋‹ค.
  • ํ•˜์ง€๋งŒ ๊ฐ•์˜์‹ค์˜ ๊ฒฝ์šฐ๋Š” ํ•™์ƒ ๋ฒˆํ˜ธ๊ฐ€ ์—†์–ด๋„ ๊ฒฐ์ •ํ•  ์ˆ˜ ์žˆ๋‹ค. -> ์ด๋Š” Full dependency๊ฐ€ ์•„๋‹Œ Partial dependency๋ฅผ ๊ฐ€์ง„๋‹ค. (๋ชจ๋“  PK๋ฅผ ์‚ฌ์šฉํ•ด ๊ฒฐ์ •๋˜๋Š” ๊ฒƒ์ด ์•„๋‹ˆ๋ฏ€๋กœ)

๋”ฐ๋ผ์„œ, ์•„๋ž˜์™€ ๊ฐ™์€ ํ˜•ํƒœ๋กœ ๋ถ„๋ฆฌํ•˜์—ฌ Full Dependency๋ฅผ ๋งŒ์กฑํ•˜๋„๋ก ํ•  ์ˆ˜ ์žˆ๋‹ค.

๐Ÿชš Third Normal Form(3NF)

3NF์— ๋Œ€ํ•ด ๋ฐฐ์šฐ๊ธฐ ์ „์— ์•„๋ž˜ ๊ฐœ๋…์„ ๋จผ์ € ๋‹ค๋ฃจ๊ณ  ๋„˜์–ด๊ฐ€๋„๋ก ํ•œ๋‹ค.

Transitive Functional Dependency

  • X -> Z๋ผ๋Š” FD๊ฐ€ X -> Y, Y -> Z๋กœ ๋‚˜๋ˆ ์งˆ ์ˆ˜ ์žˆ์„ ๋•Œ, Y๋Š” candidate key๋„ ์•„๋‹ˆ๊ณ , Key์— ํฌํ•จ๋˜์ง€ ์•Š์•„์•ผ ํ•œ๋‹ค.

    • ์ฆ‰, Y๊ฐ€ Non prime attribute์—ฌ์•ผ ํ•œ๋‹ค.
  • ์ด๋•Œ Transitive Functional Dependency๊ฐ€ ์žˆ๋‹ค๊ณ  ํ•œ๋‹ค.

2NF๋ฅผ ๋งŒ์กฑํ•˜๊ณ , Transitive Functional Dependency๊ฐ€ ์—†๋Š” ๊ฒฝ์šฐ์ด๋‹ค.

  • Prime Attr -> Non prime Attr -> Non prime Attr ์—ฌ์•ผ Transitive Functional Dependency๊ฐ€ ์กด์žฌํ•˜๊ธฐ์—, 3NF๋ฅผ ๋งŒ์กฑํ•˜๊ธฐ ์œ„ํ•ด์„œ๋Š” Prime Attr -> Prime Attr -> Non prime Attr ์—ฌ์•ผ ํ•œ๋‹ค.
  • ์œ„ ๊ทธ๋ฆผ์—์„œ๋Š” {ํ•™์ƒ ๋ฒˆํ˜ธ}, {๊ฐ•์ขŒ ์ด๋ฆ„}์„ ์ €์žฅํ•˜๋Š” relation๊ณผ, {๊ฐ•์ขŒ ์ด๋ฆ„}, {์ˆ˜๊ฐ•๋ฃŒ}๋ฅผ ์ €์žฅํ•˜๋Š” relation์œผ๋กœ ๋‚˜๋ˆ„์–ด 3NF๋ฅผ ๋งŒ์กฑ์‹œํ‚ฌ ์ˆ˜ ์žˆ๋‹ค.

3NF๋Š” 2๊ฐ€์ง€ ์œ ํ˜•์˜ violation์„ ๊ฐ์ง€ํ•  ์ˆ˜ ์žˆ๋‹ค.

  1. 2NF ์œ„๋ฐ˜ ๊ฐ์ง€

Prime attribute๊ฐ€ functionallyํ•˜๊ฒŒ Non-prime attribute๋ฅผ ๊ฒฐ์ •ํ•˜๋Š” ๊ฒฝ์šฐ, Partial Functional Dependency๋กœ ์ธํ•ด 2NF์˜ ์œ„๋ฐ˜์„ ๊ฐ์ง€ํ•  ์ˆ˜ ์žˆ๋‹ค.

  1. 3NF ์œ„๋ฐ˜ ๊ฐ์ง€

2NF๋กœ ์ธํ•ด ๋ชจ๋“  Non prime attribute๋Š” Prime attribute์— ์˜์กดํ•œ๋‹ค.

Non prime attribute๊ฐ€ Non prime attribute๋ฅผ ๊ฒฐ์ •ํ•˜๋Š” ๊ฒฝ์šฐ, Prime attribute -> Non prime attribute -> Non prime attribute์˜ ํ˜•ํƒœ๋ฅผ ๊ฐ€์ง€๊ธฐ์—, Transitive Dependency๋กœ ์ธํ•ด 3NF์˜ ์œ„๋ฐ˜์„ ๊ฐ์ง€ํ•  ์ˆ˜ ์žˆ๋‹ค.

relation R์˜ ๋ชจ๋“  Non prime attribute๊ฐ€ ์•„๋ž˜ ๋‘ ๊ฒฝ์šฐ๋ฅผ ๋งŒ์กฑํ•˜๋Š” ๊ฒฝ์šฐ 3NF๋ฅผ ๋งŒ์กฑํ•œ๋‹ค.

  • a. R์˜ ๋ชจ๋“  primary key์— fully functionally dependent
  • b. R์˜ ๋ชจ๋“  candidate key์— non-transitively dependent

์–ด๋–ค relation์ด 3NF๋ฅผ ๋งŒ์กฑํ•œ๋‹ค๋ฉด, a. ํŠน์„ฑ์œผ๋กœ ์ธํ•ด 2NF๋„ ์ž๋™์œผ๋กœ ๋งŒ์กฑํ•œ๋‹ค.

ํ•˜์ง€๋งŒ b. ํŠน์„ฑ์€ 3NF๋ฅผ ๋งŒ์กฑํ•˜๊ฒŒ ํ•˜์ง€๋งŒ, BCNF๋ฅผ ๋งŒ์กฑํ•˜์ง€ ๋ชปํ•˜๋„๋ก ํ•˜๋Š” ์กฐ๊ฑด์ด๋‹ค.

  • BCNF๋Š” candidate key์— ์˜ํ•ด ๋ฐœ์ƒํ•˜๋Š” transitive property ๋˜ํ•œ ์—†์• ๋Š” ์ •๊ทœํ™”์ด๋‹ค.

๐Ÿชš Boyce-Codd Normal Form(BCNF)

relation R์—์„œ X -> A๋ผ๋Š” FD๊ฐ€ ์žˆ์„ ๋•Œ, X๊ฐ€ R์˜ super key์ธ ๊ฒฝ์šฐ์ด๋‹ค.

  • ๊ธฐ๋ณธ ํ‚ค๋Š” {ํ•™์ƒ ๋ฒˆํ˜ธ, ํŠน๊ฐ• ์ด๋ฆ„} ์ด๊ณ , ๊ต์ˆ˜๋Š” ์ด์— ๋Œ€ํ•ด ์™„์ „ํžˆ ์ข…์†ํ•˜๊ณ  ์žˆ๋‹ค.
  • ๊ต์ˆ˜๋„ ํŠน๊ฐ• ์ด๋ฆ„์„ ๊ฒฐ์ •ํ•˜๋ฉฐ, ๊ฒฐ์ •์ž์˜ ์—ญํ• ์„ ์ˆ˜ํ–‰ํ•œ๋‹ค.
  • ํ•˜์ง€๋งŒ ๊ต์ˆ˜๋Š” candidate key๊ฐ€ ์•„๋‹Œ Non prime attribute์ด๋‹ค.

๋”ฐ๋ผ์„œ, 3NF๋Š” ๋งŒ์กฑํ•˜์ง€๋งŒ, BCNF๋Š” ๋งŒ์กฑํ•˜์ง€ ์•Š์•„ ์•„๋ž˜์™€ ๊ฐ™์ด ๋ณ€๊ฒฝํ•ด์•ผ ํ•œ๋‹ค.

๐Ÿชš ๋ฌด์†์‹ค ๋ถ„ํ•ด

์ด์ƒํ˜„์ƒ์„ ํ•ด๊ฒฐํ•˜๊ธฐ ์œ„ํ•ด ์ •๊ทœํ™”๋ฅผ ํ†ตํ•ด relation์„ ๋ถ„ํ•ดํ•ด์•ผ ํ•œ๋‹ค. ํ•˜์ง€๋งŒ ๋ถ„ํ•ด ์ดํ›„, ๋ถ„ํ•ด ์ „๊ณผ ๊ฒฐ๊ณผ๊ฐ€ ๋‹ฌ๋ผ์ง€๋ฉด ์†์‹ค์ด ๋ฐœ์ƒํ•˜๋Š” ๊ฒƒ์ด๋ฏ€๋กœ, ์†์‹ค์ด ์—†๋Š” ๋ถ„ํ•ด๋ฅผ ์ˆ˜ํ–‰ํ•ด์•ผ ํ•œ๋‹ค.

์ด๋•Œ, ์†์‹ค์ด ์—†๋Š” ๋ถ„ํ•ด๋ฅผ ๋ฌด์†์‹ค ๋ถ„ํ•ด(Lossless join decomposition)๋ผ๊ณ  ํ•œ๋‹ค.

๋”ฐ๋ผ์„œ, relation ๋ถ„ํ•ด ์‹œ non-additive(lossless) join property์™€ functional dependency preservation property๋ฅผ ๋งŒ์กฑํ•˜๋Š” ๋ถ„ํ•ด๋ฅผ ์ˆ˜ํ–‰ํ•ด์•ผ ํ•œ๋‹ค.

  • ๊ทธ๋ฆฌ๊ณ  ์ด ๋‘ ์กฐ๊ฑด ์ค‘์—์„œ๋Š” lossless join property๊ฐ€ ์šฐ์„ ์ˆœ์œ„๋ฅผ ๊ฐ€์ ธ, ์ ˆ๋Œ€ ํฌ์ƒ๋˜์–ด์„  ์•ˆ๋œ๋‹ค.

๐Ÿ“• ์ฐธ๊ณ 

@Seongukbaek
๋ฐฐ์šฐ๊ณ  ์ •๋ฆฌํ•˜๊ณ  ๊ณต์œ ํ•˜๋Š” ๋Œ€ํ•™์ƒ ๋ฐฑ์„ฑ์šฑ์ž…๋‹ˆ๋‹ค ๐Ÿ˜€